package com.design.working.dao.impl;

import com.design.working.dao.DeptDAO;
import com.design.working.dto.dept.*;
import com.design.working.po.*;
import com.design.working.po.dept.*;
import com.design.working.util.JDBCUtil;
import com.design.working.vo.dept.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.design.working.enums.OperationEnum.*;

public class DeptDAOImpl implements DeptDAO {

    /**
     * 查找本部门未审批的学生岗位申请记录
     *
     * @param uid  部门员工工号
     * @param page  分页当前页码
     * @param limit 分页每页展示数据
     * @return 查询到的本部门未审批的学生岗位申请记录
     */
    @Override
    public List<DeptHandleStudentWorkApplicationVO> selectStudentWorkApplication(Integer uid, Integer page, Integer limit) {

        List<DeptHandleStudentWorkApplicationVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT swa.id, c.name, m.name, c2.class_name, s.uid, " +
                    "s.username, j.name, j.detail, swa.student_msg_template_id " +
                    "FROM student_work_application swa " +
                    "JOIN student_msg_template smt ON swa.student_msg_template_id = smt.id " +
                    "JOIN student s ON smt.student_uid = s.uid " +
                    "JOIN classes c2 ON smt.class_id = c2.id " +
                    "JOIN major m ON c2.major_id = m.id " +
                    "JOIN college c ON c.id = m.college_id " +
                    "JOIN job j ON j.id = swa.job_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "WHERE swa.is_counselor_passed <> 0 AND swa.is_dept_passed = 0 " +
                    "AND swa.is_deleted = 0 AND de.uid = ?";

            //分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }
            //查找本部门未处理的学生岗位申请
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                //专业班级
                String majorClass = resultSet.getString(3) + "-" + resultSet.getString(4);
                list.add(new DeptHandleStudentWorkApplicationVO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        majorClass,
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询当前部门员工是否有负责的在运行岗位
     *
     * @param deptEmployeeId 部门员工id
     * @return true-有; false-没有
     */
    @Override
    public boolean isDeptHaveResponsibleRunningJob(Integer deptEmployeeId) {

        boolean flag = false;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id FROM job WHERE is_deleted = 0 AND dept_employee_id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);

            resultSet = preparedStatement.executeQuery();
            //一个部门员工可能负责多个岗位，这里只需要判断有没有，所以用if
            if (resultSet.next()) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return flag;
    }

    /**
     * 审批学生的岗位申请
     *
     * @param deptEmployeeId 部门员工id
     * @param deptHandleStudentApplicationDTO 部门员工对学生申请记录的审批内容
     */
    @Override
    public void insertStudentApplicationHanding(Integer deptEmployeeId, DeptHandleStudentApplicationDTO deptHandleStudentApplicationDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE student_work_application " +
                    "SET dept_employee_id = ?,dept_text = ?,is_dept_passed = ? " +
                    "WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);
            preparedStatement.setString(2, deptHandleStudentApplicationDTO.getAdvice());
            preparedStatement.setInt(3, deptHandleStudentApplicationDTO.getOperation());
            preparedStatement.setInt(4, deptHandleStudentApplicationDTO.getId());
            //变更该条学生岗位申请记录的信息
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 通过工号查找部门员工在数据库中id
     *
     * @param uid 部门员工工号
     * @return 部门员工在数据库中id
     */
    @Override
    public int selectDeptEmployeeId(Integer uid){

        int id = NO_INDEX.getVal();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id " +
                    "FROM dept_employee " +
                    "WHERE uid = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return id;
    }

    /**
     * 查找申请表中学生申请工作填写的个人信息
     *
     * @param smtId 学生申请工作表中对应的个人信息记录id
     * @return 学生申请工作填写的个人信息
     */
    @Override
    public StudentApplicationDetailPO selectStudentApplicationDetail(Integer smtId){

        StudentApplicationDetailPO studentApplicationDetailPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT phone, email, political_status, needy_student, dorm, strength, free_time " +
                    "FROM student_msg_template " +
                    "WHERE id = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, smtId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationDetailPO = new StudentApplicationDetailPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationDetailPO;
    }

    /**
     * 查找辅导员对学生申请表的意见
     *
     * @param swaId 学生申请工作表id
     * @return 辅导员对学生申请表的意见
     */
    @Override
    public DeptPreAdvicePO selectStudentPreAdvice(Integer swaId) {

        DeptPreAdvicePO deptPreAdvicePO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed " +
                    "FROM student_work_application swa " +
                    "JOIN counselor c on swa.counselor_id = c.id " +
                    "WHERE swa.id = ? AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                deptPreAdvicePO = new DeptPreAdvicePO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return deptPreAdvicePO;
    }

    /**
     * 查询学生岗位变动申请
     *
     * @param uid   部门员工工号
     * @param page  分页参数
     * @param limit 每页条数
     * @return 查询到的本部门未审批的学生岗位变动申请
     */
    @Override
    public List<DeptHandleStudentWorkMotionPO> selectStudentWorkMotion(Integer uid, Integer page, Integer limit) {

        List<DeptHandleStudentWorkMotionPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT swm.id, c2.name, m.name, c.class_name, s.uid, s.username, " +
                    "j.name, swm.work_type, swm.student_explanation " +
                    "FROM student_work_motion swm " +
                    "JOIN student_to_work stw on swm.student_to_work_id = stw.id " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN student_msg sm on stw.student_uid = sm.uid " +
                    "JOIN student s on sm.uid = s.uid " +
                    "JOIN classes c on sm.class_id = c.id " +
                    "JOIN major m on c.major_id = m.id " +
                    "JOIN college c2 on m.college_id = c2.id " +
                    "JOIN dept_employee de on j.dept_employee_id = de.id " +
                    "WHERE swm.is_dept_passed = 0 AND swm.is_deleted = 0 AND de.uid = ?";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptHandleStudentWorkMotionPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getInt(8),
                        resultSet.getString(9)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 插入部门员工处理的学生岗位变更表意见
     *
     * @param deptEmployeeId                    部门员工id
     * @param deptHandleStudentMotionDTO 前端传来的表单数据
     */
    @Override
    public void insertStudentMotionHanding(Integer deptEmployeeId, DeptHandleStudentMotionDTO deptHandleStudentMotionDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE student_work_motion " +
                    "SET dept_employee_id = ?, dept_explanation = ?, is_dept_passed = ? " +
                    "WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);
            preparedStatement.setString(2, deptHandleStudentMotionDTO.getAdvice());
            preparedStatement.setInt(3, deptHandleStudentMotionDTO.getOperation());
            preparedStatement.setInt(4, deptHandleStudentMotionDTO.getId());
            //变更该条学生岗位变动申请记录的信息
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 查询岗位学生信息
     *
     * @param uid   部门员工工号
     * @param page  分页参数
     * @param limit 每页条数
     * @return 查询到的本部门的岗位学生信息
     */
    @Override
    public List<DeptSelectJobStudentInfoPO> selectJobStudentInfo(Integer uid, Integer page, Integer limit) {

        List<DeptSelectJobStudentInfoPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c2.name, m.name, c.class_name, s.uid, s.username, ct.year, " +
                    "ct.term, j.name, stw.status, j.detail " +
                    "FROM student_to_work stw " +
                    "JOIN student s ON stw.student_uid = s.uid " +
                    "JOIN job j ON j.id = stw.job_id " +
                    "JOIN campus_term ct ON ct.id = j.campus_term_id " +
                    "JOIN student_msg sm ON sm.uid = s.uid " +
                    "JOIN classes c ON c.id = sm.class_id " +
                    "JOIN major m ON m.id = c.major_id " +
                    "JOIN college c2 ON c2.id = m.college_id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE stw.is_deleted = 0 AND dem.uid = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptSelectJobStudentInfoPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getInt(9),
                        resultSet.getString(10)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查询岗位学生信息
     *
     * @param uid         部门员工工号
     * @param page        分页参数
     * @param limit       每页条数
     * @param stuUid      学生学号
     * @param studentName 学生姓名
     * @return 查询到的本部门的岗位学生信息
     */
    @Override
    public List<DeptSelectJobStudentInfoPO> selectJobStudentInfoByName(Integer uid, Integer page, Integer limit, String stuUid, String studentName) {

        List<DeptSelectJobStudentInfoPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c2.name, m.name, c.class_name, s.uid, s.username, ct.year, " +
                    "ct.term, j.name, stw.status, j.detail " +
                    "FROM student_to_work stw " +
                    "JOIN student s ON stw.student_uid = s.uid " +
                    "JOIN job j ON j.id = stw.job_id " +
                    "JOIN campus_term ct ON ct.id = j.campus_term_id " +
                    "JOIN student_msg sm ON sm.uid = s.uid " +
                    "JOIN classes c ON c.id = sm.class_id " +
                    "JOIN major m ON m.id = c.major_id " +
                    "JOIN college c2 ON c2.id = m.college_id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE stw.is_deleted = 0 AND dem.uid = ? AND INSTR(s.uid, ?) > 0 AND INSTR(s.username, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            preparedStatement.setString(2, stuUid);
            preparedStatement.setString(3, studentName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptSelectJobStudentInfoPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getInt(9),
                        resultSet.getString(10)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生详细信息
     *
     * @param uid 学生学号
     * @return 学生个人详细信息
     */
    @Override
    public StudentMsgPO selectStudentMsg(Integer uid) {

        StudentMsgPO studentMsgPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT gender, phone, email, political_status, is_needy_student, dorm, strength " +
                    "FROM student_msg " +
                    "WHERE uid = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentMsgPO = new StudentMsgPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentMsgPO;
    }

    /**
     * 查询当前部门员工所属部门的学生岗位申请信息
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 查询到的本部门的学生岗位申请信息
     */
    @Override
    public List<DeptSelectStudentApplicationPO> selectStudentApplicationRecord(Integer uid, Integer page, Integer limit) {

        List<DeptSelectStudentApplicationPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, j.name, " +
                    "swa.is_dept_passed, j.detail, swa.id, smt.id " +
                    "FROM student_work_application swa " +
                    "JOIN student_msg_template smt on swa.student_msg_template_id = smt.id " +
                    "JOIN student s on s.uid = smt.student_uid " +
                    "JOIN job j on swa.job_id = j.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE swa.is_deleted = 0 AND swa.is_counselor_passed <> 0 AND dem.uid = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptSelectStudentApplicationPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getString(7),
                        resultSet.getInt(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查询当前部门员工所属部门的学生岗位申请信息
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param stuUid      学生学号
     * @param studentName 学生姓名
     * @return 查询到的本部门的学生岗位申请信息
     */
    @Override
    public List<DeptSelectStudentApplicationPO> selectStudentApplicationRecordByName(Integer uid, Integer page, Integer limit, String stuUid, String studentName){

        List<DeptSelectStudentApplicationPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, j.name, " +
                    "swa.is_dept_passed, j.detail, swa.id, smt.id " +
                    "FROM student_work_application swa " +
                    "JOIN student_msg_template smt on swa.student_msg_template_id = smt.id " +
                    "JOIN student s on s.uid = smt.student_uid " +
                    "JOIN job j on swa.job_id = j.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE INSTR(s.uid, ?) > 0 AND INSTR(s.username, ?) > 0 " +
                    "AND swa.is_deleted = 0 AND swa.is_counselor_passed <> 0 AND dem.uid = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, stuUid);
            preparedStatement.setString(2, studentName);
            preparedStatement.setInt(3, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptSelectStudentApplicationPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getString(7),
                        resultSet.getInt(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询三者都已审批的学生岗位申请的审批详情
     * @param swaId 学生岗位申请表id
     * @return 查询到的三者都已审批的学生岗位申请的审批详情
     */
    @Override
    public StudentApplicationExaminationPO selectStuAppExamination(Integer swaId) {

        StudentApplicationExaminationPO studentApplicationExaminationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed, de.username, " +
                    "dept_text, is_dept_passed, a.username, admin_text, is_admin_passed " +
                    "FROM student_work_application swa " +
                    "JOIN counselor c on swa.counselor_id = c.id " +
                    "JOIN admin a on swa.admin_id = a.id " +
                    "JOIN dept_employee de on swa.dept_employee_id = de.id " +
                    "WHERE swa.id = ? AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO = new StudentApplicationExaminationPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询部门员工和管理员都未审批的学生岗位申请审批详情
     * @param swaId 学生岗位申请表id
     * @return 查询到的部门员工和管理员都未审批的学生岗位申请审批详情
     */
    @Override
    public StudentApplicationExaminationPO selectStuAppExamination0(Integer swaId) {

        StudentApplicationExaminationPO studentApplicationExaminationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed " +
                    "FROM student_work_application swa " +
                    "JOIN counselor c on swa.counselor_id = c.id " +
                    "WHERE swa.id = ? AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO = new StudentApplicationExaminationPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        "",
                        "",
                        0,
                        "",
                        "",
                        0
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询管理员未审批的学生岗位申请审批详情
     * @param swaId 学生岗位申请表id
     * @return 查询到的管理员未审批的学生岗位申请审批详情
     */
    @Override
    public StudentApplicationExaminationPO selectStuAppExamination1(Integer swaId) {

        StudentApplicationExaminationPO studentApplicationExaminationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed, " +
                    "de.username, dept_text, is_dept_passed " +
                    "FROM student_work_application swa " +
                    "JOIN counselor c on swa.counselor_id = c.id " +
                    "JOIN dept_employee de on swa.dept_employee_id = de.id " +
                    "WHERE swa.id = ? AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO = new StudentApplicationExaminationPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        "",
                        "",
                        0
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询当前学生岗位申请是否已被部门员工和管理员审批
     *
     * @param swaId 学生岗位申请表id
     * @return 0-两者都未审批; 1-部门员工已审批；2-部门员工和管理员都已审批
     */
     public int isHandleStuApp(Integer swaId) {

        int handleType = 0;//默认都未审批

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();

            String sql1 = "SELECT id FROM student_work_application WHERE id = ? AND is_dept_passed <> 0";
            String sql2 = "SELECT id FROM student_work_application WHERE id = ? AND is_admin_passed <> 0";

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                handleType = 2;
                return handleType;
            }

            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                handleType = 1;
                return handleType;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return handleType;
    }

    /**
     * 查询当前部门领导所属部门的全部在运行岗位
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 查询到的当前部门领导所属部门的全部在运行岗位
     */
    @Override
    public List<DeptSelectWorkingJobVO> selectDeptWorkingJob(Integer uid, Integer page, Integer limit) {

        List<DeptSelectWorkingJobVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT j.id, j.name, jc.name, j.place, j.salary, " +
                    "de.username, jr.remain, j.detail " +
                    "FROM job j " +
                    "JOIN dept_employee_msg dem ON j.dept_id = dem.dept_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN job_remain jr ON jr.job_id = j.id " +
                    "WHERE j.is_deleted = 0 AND dem.uid = ?";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                // 设置是否可申请删除该岗位
                int choice = 1;
                if (isDeptJobInDeleteApplying(resultSet.getInt(1), uid)) {
                    choice = 0;
                }
                list.add(new DeptSelectWorkingJobVO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getBigDecimal(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        choice)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过岗位名称查询当前部门领导所属部门的在运行岗位
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param jobName     搜索的岗位的岗位名称
     * @return 查询到的当前部门领导所属部门的在运行岗位
     */
    @Override
    public List<DeptSelectWorkingJobVO> selectDeptWorkingJobByName(Integer uid, Integer page, Integer limit, String jobName) {

        List<DeptSelectWorkingJobVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT j.id, j.name, jc.name, j.place, j.salary, " +
                    "de.username, jr.remain, j.detail " +
                    "FROM job j " +
                    "JOIN dept_employee_msg dem ON j.dept_id = dem.dept_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN job_remain jr ON jr.job_id = j.id " +
                    "WHERE j.is_deleted = 0 AND dem.uid = ? AND INSTR(j.name, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            preparedStatement.setString(2, jobName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                // 设置是否可申请删除该岗位
                int choice = 1;
                if (isDeptJobInDeleteApplying(resultSet.getInt(1), uid)) {
                    choice = 0;
                }
                list.add(new DeptSelectWorkingJobVO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getBigDecimal(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        choice)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询当前部门领导是否正在申请删除该岗位即处于待处理状态中
     *
     * @param jobId   岗位id
     * @param uid     部门员工工号
     * @return true-有; false-没有
     */
    public boolean isDeptJobInDeleteApplying(Integer jobId, Integer uid) {

        boolean flag = false;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT djm.id " +
                    "FROM dept_job_motion djm " +
                    "JOIN dept_employee de ON djm.dept_employee_id = de.id " +
                    "WHERE djm.job_id = ? AND de.uid = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, jobId);
            preparedStatement.setInt(2, uid);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return flag;
    }

    /**
     * 插入部门领导申请删除岗位的申请信息
     *
     * @param deptEmployeeId            部门员工id
     * @param deptDeleteJobMotionDTO    前端传来的申请信息
     */
    @Override
    public void insertDeptDeleteJobMotion(Integer deptEmployeeId, DeptDeleteJobMotionDTO deptDeleteJobMotionDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "INSERT INTO dept_job_motion(dept_employee_id, job_type, job_id, dept_explanation) " +
                    "VALUES(?, ?, ?, ?)";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);
            preparedStatement.setInt(2, deptDeleteJobMotionDTO.getWorkType());
            preparedStatement.setInt(3, deptDeleteJobMotionDTO.getId());
            preparedStatement.setString(4, deptDeleteJobMotionDTO.getDeptEmployeeExplanation());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 插入部门领导申请发布岗位的申请信息
     *
     * @param deptEmployeeId            部门员工id
     * @param deptAddJobApplicationDTO  前端表单传来的信息
     */
    @Override
    public void insertDeptAddJobApplication(Integer deptEmployeeId, DeptAddJobApplicationDTO deptAddJobApplicationDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            // 关闭自动提交事务
            connection.setAutoCommit(false);

            String sql1 = "SELECT id FROM campus_term WHERE year = ? AND term = ?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, deptAddJobApplicationDTO.getYear());
            preparedStatement.setInt(2, deptAddJobApplicationDTO.getTerm());
            // 1. 先查询选择学期的id
            resultSet = preparedStatement.executeQuery();
            int campusTermId = NO_INDEX.getVal();
            if (resultSet.next()) {
                campusTermId = resultSet.getInt(1);
            }

            String sql2 = "SELECT d.id " +
                    "FROM dept d " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = d.id " +
                    "JOIN dept_employee de ON de.uid = dem.uid " +
                    "WHERE de.id = ?";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, deptEmployeeId);
            // 2. 查询当前部门领导所在部门的id
            resultSet = preparedStatement.executeQuery();
            int deptId = NO_INDEX.getVal();
            if (resultSet.next()) {
                deptId = resultSet.getInt(1);
            }

            if (campusTermId <= NOT_FIND_INDEX.getVal()) {
                connection.rollback();
            } else {
                String sql3 = "INSERT INTO dept_job_application(dept_id, dept_employee_id, job_name, place, job_detail, " +
                        "need_count, job_category_id, campus_term_id) " +
                        "VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
                preparedStatement = connection.prepareStatement(sql3);
                preparedStatement.setInt(1, deptId);
                preparedStatement.setInt(2, deptEmployeeId);
                preparedStatement.setString(3, deptAddJobApplicationDTO.getName());
                preparedStatement.setString(4, deptAddJobApplicationDTO.getPlace());
                preparedStatement.setString(5, deptAddJobApplicationDTO.getWorkDetail());
                preparedStatement.setInt(6, deptAddJobApplicationDTO.getNeedCount());
                preparedStatement.setInt(7, deptAddJobApplicationDTO.getJobCategory());
                preparedStatement.setInt(8, campusTermId);
                preparedStatement.executeUpdate();

                // 提交事务
                connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
    }

    /**
     * 查询当前部门领导的发布岗位申请的审核信息
     * @param deptEmployeeId   部门员工id
     * @param page             分页页数
     * @param limit            每页数据条数
     * @return 查询到的当前部门领导的发布岗位申请的审核信息
     */
    @Override
    public List<DeptJobApplicationHandlePO> selectAddJobAppHandleRecord(Integer deptEmployeeId, Integer page, Integer limit) {

        List<DeptJobApplicationHandlePO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT dja.id, dja.job_name, jc.name, dja.place, dja.job_detail, " +
                    "dja.need_count, ct.year, ct.term, dja.admin_type " +
                    "FROM dept_job_application dja " +
                    "JOIN campus_term ct ON dja.campus_term_id = ct.id " +
                    "JOIN job_category jc ON jc.id = dja.job_category_id " +
                    "WHERE dja.dept_employee_id = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptJobApplicationHandlePO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7),
                        resultSet.getInt(8),
                        resultSet.getInt(9),
                        ""
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过岗位名称查询当前部门领导的发布岗位申请的审核信息
     * @param deptEmployeeId   部门员工id
     * @param page             分页页数
     * @param limit            每页数据条数
     * @param jobName     搜索的岗位的岗位名称
     * @return 查询到的当前部门领导的发布岗位申请的审核信息
     */
    @Override
    public List<DeptJobApplicationHandlePO> selectAddJobAppHandleRecordByName(Integer deptEmployeeId, Integer page, Integer limit, String jobName) {

        List<DeptJobApplicationHandlePO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT dja.id, dja.job_name, jc.name, dja.place, dja.job_detail, " +
                    "dja.need_count, ct.year, ct.term, dja.admin_type " +
                    "FROM dept_job_application dja " +
                    "JOIN campus_term ct ON dja.campus_term_id = ct.id " +
                    "JOIN job_category jc ON jc.id = dja.job_category_id " +
                    "WHERE dja.dept_employee_id = ? AND INSTR(dja.job_name, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);
            preparedStatement.setString(2, jobName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptJobApplicationHandlePO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7),
                        resultSet.getInt(8),
                        resultSet.getInt(9),
                        ""
                ));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询当前发布岗位申请的审批信息
     * @param djaId 学生岗位申请表id
     * @return 查询到的当前发布岗位申请的审批信息
     */
    @Override
    public DeptJobAppExaminationVO selectAddJobAppExamination(Integer djaId) {

        //这里不存在影响别的审核信息，可直接在此动态返回信息
        DeptJobAppExaminationVO deptJobAppExaminationVO = new DeptJobAppExaminationVO("暂无管理员处理","无");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT a.username, dja.admin_text " +
                    "FROM dept_job_application dja " +
                    "JOIN admin a ON a.id = dja.admin_id " +
                    "WHERE dja.id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, djaId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                deptJobAppExaminationVO = new DeptJobAppExaminationVO(
                        resultSet.getString(1),
                        resultSet.getString(2)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return deptJobAppExaminationVO;
    }

    /**
     * 查询当前部门领导的删除岗位申请的审核信息
     * @param deptEmployeeId  部门员工id
     * @param page            分页页数
     * @param limit           每页数据条数
     * @return 查询到的当前部门领导的删除岗位申请的审核信息
     */
    @Override
    public List<DeptJobApplicationHandlePO> selectDeleteJobAppHandleRecord(Integer deptEmployeeId, Integer page, Integer limit) {

        List<DeptJobApplicationHandlePO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT djm.id, j.name, jc.name, j.place, j.detail, dja.need_count, " +
                    "ct.year, ct.term, djm.is_admin_passed, djm.dept_explanation " +
                    "FROM dept_job_motion djm " +
                    "JOIN job j ON j.id = djm.job_id " +
                    "JOIN campus_term ct ON ct.id = j.campus_term_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN dept_job_application dja ON dja.job_name = j.name " +
                    "WHERE djm.is_deleted = 0 AND djm.dept_employee_id = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptJobApplicationHandlePO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7),
                        resultSet.getInt(8),
                        resultSet.getInt(9),
                        resultSet.getString(10)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过岗位名称查询当前部门领导的删除岗位申请的审核信息
     * @param deptEmployeeId  部门员工id
     * @param page            分页页数
     * @param limit           每页数据条数
     * @param jobName         搜索的岗位的岗位名称
     * @return 查询到的当前部门领导的删除岗位申请的审核信息
     */
    @Override
    public List<DeptJobApplicationHandlePO> selectDeleteJobAppHandleRecordByName(Integer deptEmployeeId, Integer page, Integer limit, String jobName) {

        List<DeptJobApplicationHandlePO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT djm.id, j.name, jc.name, j.place, j.detail, dja.need_count, " +
                    "ct.year, ct.term, djm.is_admin_passed, djm.dept_explanation " +
                    "FROM dept_job_motion djm " +
                    "JOIN job j ON j.id = djm.job_id " +
                    "JOIN campus_term ct ON ct.id = j.campus_term_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN dept_job_application dja ON dja.job_name = j.name " +
                    "WHERE djm.is_deleted = 0 AND djm.dept_employee_id = ? AND INSTR(j.name, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);
            preparedStatement.setString(2, jobName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new DeptJobApplicationHandlePO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getInt(7),
                        resultSet.getInt(8),
                        resultSet.getInt(9),
                        resultSet.getString(10)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询当前删除岗位申请的审批信息
     * @param djmId 学生岗位变动表id
     * @return 查询到的当前删除岗位申请的审批信息
     */
    @Override
    public DeptJobAppExaminationVO selectDeleteJobAppExamination(Integer djmId) {

        DeptJobAppExaminationVO deptJobAppExaminationVO = new DeptJobAppExaminationVO("暂无管理员处理","无");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT a.username, djm.admin_explanation " +
                    "FROM dept_job_motion djm " +
                    "JOIN admin a ON a.id = djm.admin_id " +
                    "WHERE djm.id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, djmId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                deptJobAppExaminationVO = new DeptJobAppExaminationVO(
                        resultSet.getString(1),
                        resultSet.getString(2)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return deptJobAppExaminationVO;
    }

    /**
     * 查询当前部门员工是否为部门领导（领导才能发布/删除岗位和查询岗位审核记录）
     *
     * @param  uid  部门员工工号
     * @return true-是; false-不是
     */
    @Override
    public boolean isLeaderToDept(Integer uid) {

        boolean flag = false;

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id FROM dept_employee_msg WHERE dept_employee_msg.uid = ? AND is_leader = 1";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return flag;
    }

    /**
     * 查询当前部门员工所属部门的全部已下线岗位
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 查询到的当前部门员工所属部门的全部已下线岗位
     */
    @Override
    public List<DeptSelectOfflineJobVO> selectDeptOfflineJob(Integer uid, Integer page, Integer limit) {

        List<DeptSelectOfflineJobVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT j.id, j.name, jc.name, j.place, " +
                    "j.salary, de.username, j.detail " +
                    "FROM job j " +
                    "JOIN dept_employee_msg dem ON j.dept_id = dem.dept_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "WHERE j.is_deleted = 1 AND dem.uid = ?";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                list.add(new DeptSelectOfflineJobVO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getBigDecimal(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过岗位名称查询当前部门员工所属部门的已下线岗位
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param jobName     搜索的岗位的岗位名称
     * @return 查询到的当前部门员工所属部门的已下线岗位
     */
    @Override
    public List<DeptSelectOfflineJobVO> selectDeptOfflineJobByName(Integer uid, Integer page, Integer limit, String jobName) {

        List<DeptSelectOfflineJobVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT j.id, j.name, jc.name, j.place, " +
                    "j.salary, de.username, j.detail " +
                    "FROM job j " +
                    "JOIN dept_employee_msg dem ON j.dept_id = dem.dept_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "WHERE j.is_deleted = 1 AND dem.uid = ? AND INSTR(j.name, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            preparedStatement.setString(2, jobName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(3, page);
                preparedStatement.setInt(4, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                list.add(new DeptSelectOfflineJobVO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getBigDecimal(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询该部门已下线岗位的下线审核信息
     * @param jobId 岗位id
     * @return 查询到的该部门已下线岗位的下线审核信息
     */
    @Override
    public DeptOfflineJobExaminationVO selectDeptOfflineJobExamination(Integer jobId){

        DeptOfflineJobExaminationVO deptOfflineJobExaminationVO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT de.username, dept_explanation, a.username, admin_explanation " +
                    "FROM dept_job_motion djm " +
                    "JOIN dept_employee de ON de.id = djm.dept_employee_id " +
                    "JOIN admin a ON a.id = djm.admin_id " +
                    "WHERE job_id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, jobId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                deptOfflineJobExaminationVO = new DeptOfflineJobExaminationVO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return deptOfflineJobExaminationVO;
    }

    /**
     * 查询目前属于在岗状态的岗位学生信息
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 查询到的目前属于在岗状态的岗位学生信息
     */
    @Override
    public List<DeptSelectWorkingJobStudentVO> selectDeptWorkingJobStudent(Integer uid, Integer page, Integer limit) {

        List<DeptSelectWorkingJobStudentVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT stw.id, c2.name, m.name, c.class_name, s.uid, s.username, " +
                    "j.name, jc.name, j.place, de.username, j.detail " +
                    "FROM student_to_work stw " +
                    "JOIN student s ON stw.student_uid = s.uid " +
                    "JOIN job j ON j.id = stw.job_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN student_msg sm ON sm.uid = s.uid " +
                    "JOIN classes c ON c.id = sm.class_id " +
                    "JOIN major m ON m.id = c.major_id " +
                    "JOIN college c2 ON c2.id = m.college_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE stw.is_deleted = 0 AND stw.status = 1 AND dem.uid = ?";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                String majorClass = resultSet.getString(3) + "-" + resultSet.getString(4);
                list.add(new DeptSelectWorkingJobStudentVO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        majorClass,
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查询目前属于在岗状态的岗位学生信息
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param stuUid      学生学号
     * @param studentName 学生姓名
     * @return 查询到的目前属于在岗状态的岗位学生信息
     */
    @Override
    public List<DeptSelectWorkingJobStudentVO> selectDeptWorkingJobStudentByName(Integer uid, Integer page, Integer limit, String stuUid, String studentName) {

        List<DeptSelectWorkingJobStudentVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT stw.id, c2.name, m.name, c.class_name, s.uid, s.username, " +
                    "j.name, jc.name, j.place, de.username, j.detail " +
                    "FROM student_to_work stw " +
                    "JOIN student s ON stw.student_uid = s.uid " +
                    "JOIN job j ON j.id = stw.job_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN student_msg sm ON sm.uid = s.uid " +
                    "JOIN classes c ON c.id = sm.class_id " +
                    "JOIN major m ON m.id = c.major_id " +
                    "JOIN college c2 ON c2.id = m.college_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE stw.is_deleted = 0 AND stw.status = 1 AND dem.uid = ? AND INSTR(s.uid, ?) > 0 AND INSTR(s.username, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            preparedStatement.setString(2, stuUid);
            preparedStatement.setString(3, studentName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                String majorClass = resultSet.getString(3) + "-" + resultSet.getString(4);
                list.add(new DeptSelectWorkingJobStudentVO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        majorClass,
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 插入部门员工考勤学生岗位信息
     *
     * @param deptEmployeeId              部门员工id
     * @param deptHandleStudentAttendDTO  前端表单传来的考勤信息
     */
    @Override
    public void insertDeptStudentAttendInfo(Integer deptEmployeeId, DeptHandleStudentAttendDTO deptHandleStudentAttendDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "INSERT INTO student_work_attendance(student_to_work_id, working_date, is_attend, dept_employee_id) " +
                    "VALUES(?, ?, ?, ?)";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptHandleStudentAttendDTO.getStwId());
            preparedStatement.setString(2, deptHandleStudentAttendDTO.getHandleDate());
            preparedStatement.setInt(3, deptHandleStudentAttendDTO.getIsAttend());
            preparedStatement.setInt(4, deptEmployeeId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 查询该部门岗位的学生考勤记录
     *
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 查询到的该部门岗位的学生考勤记录
     */
    @Override
    public List<DeptStudentAttendanceRecordPO> selectDeptStudentAttendance(Integer uid, Integer page, Integer limit) {

        List<DeptStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, j.name, " +
                    "de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student s on stw.student_uid = s.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE swa.is_deleted = 0 AND dem.uid = ?";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                list.add(new DeptStudentAttendanceRecordPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getInt(8)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查询该部门岗位的学生考勤记录
     *
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param searchDTO   搜索的信息
     * @return 查询到的通过学号姓名查询该部门岗位的学生考勤记录
     */
    @Override
    public List<DeptStudentAttendanceRecordPO> selectDeptStudentAttendanceByName(Integer uid, Integer page, Integer limit, SearchDeptStudentAttendanceDTO searchDTO) {

        List<DeptStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, j.name, " +
                    "de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student s on stw.student_uid = s.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE swa.is_deleted = 0 AND dem.uid = ? AND INSTR(s.uid, ?) > 0 " +
                    "AND INSTR(s.username, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            preparedStatement.setString(2, searchDTO.getId());
            preparedStatement.setString(3, searchDTO.getName());

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                list.add(new DeptStudentAttendanceRecordPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getInt(8)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名和时间范围查询该部门岗位的学生考勤记录
     *
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param searchDTO   搜索的信息
     * @return 查询到的通过学号姓名和时间范围查询该部门岗位的学生考勤记录
     */
    @Override
    public List<DeptStudentAttendanceRecordPO> selectDeptStudentAttendanceByTime(Integer uid, Integer page, Integer limit, SearchDeptStudentAttendanceDTO searchDTO) {

        List<DeptStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, j.name, " +
                    "de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student s on stw.student_uid = s.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "JOIN dept_employee_msg dem ON dem.dept_id = j.dept_id " +
                    "WHERE swa.is_deleted = 0 AND dem.uid = ? AND INSTR(s.uid, ?) > 0 " +
                    "AND INSTR(s.username, ?) > 0 AND swa.working_date between ? and ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            preparedStatement.setString(2, searchDTO.getId());
            preparedStatement.setString(3, searchDTO.getName());
            preparedStatement.setString(4, searchDTO.getBeginDate());
            preparedStatement.setString(5, searchDTO.getEndDate());

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(6, page);
                preparedStatement.setInt(7, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                list.add(new DeptStudentAttendanceRecordPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getInt(8)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询目前属于结岗/离岗状态的岗位学生信息
     * @param uid         部门员工工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 查询到的目前属于结岗/离岗状态的岗位学生信息
     */
    @Override
    public List<DeptSelectEndJobStudentPO> selectDeptEndJobStudent(Integer uid, Integer page, Integer limit) {

        List<DeptSelectEndJobStudentPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT stw.id, c2.name, m.name, c.class_name, s.uid, s.username, " +
                    "ct.year, ct.term, j.name, jc.name, j.place, j.detail " +
                    "FROM student_to_work stw " +
                    "JOIN student s ON stw.student_uid = s.uid " +
                    "JOIN job j ON j.id = stw.job_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN student_msg sm ON sm.uid = s.uid " +
                    "JOIN classes c ON c.id = sm.class_id " +
                    "JOIN major m ON m.id = c.major_id " +
                    "JOIN college c2 ON c2.id = m.college_id " +
                    "JOIN campus_term ct ON ct.id = j.campus_term_id " +
                    "JOIN dept_employee de ON de.id = j.dept_employee_id " +
                    "WHERE stw.is_deleted = 0 AND stw.status <> 1 AND de.uid = ?";
            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                list.add(new DeptSelectEndJobStudentPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getInt(8),
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11),
                        resultSet.getString(12)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 插入部门员工对学生的用工评价
     *
     * @param deptEmployeeId           部门员工id
     * @param deptEvaluateStudentDTO   前端表单传来的评价信息
     */
    @Override
    public void insertDeptEvaluateStuInfo(Integer deptEmployeeId, DeptEvaluateStudentDTO deptEvaluateStudentDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "INSERT INTO dept_evaluate_student(student_to_work_id, dept_employee_id, evaluation) " +
                    "VALUES(?, ?, ?)";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEvaluateStudentDTO.getStwId());
            preparedStatement.setInt(2, deptEmployeeId);
            preparedStatement.setString(3, deptEvaluateStudentDTO.getText());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 查询历史评价记录
     * @param deptEmployeeId           部门员工id
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 查询到的历史评价记录
     */
    @Override
    public List<DeptEvaluateRecordPO> selectDeptEvaluateRecord(Integer deptEmployeeId, Integer page, Integer limit) {

        List<DeptEvaluateRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT des.id, c2.name, m.name, c.class_name, s.uid, s.username, " +
                    "ct.year, ct.term, j.name, jc.name, j.place, j.detail ,des.evaluation " +
                    "FROM dept_evaluate_student des " +
                    "JOIN student_to_work stw ON stw.id = des.student_to_work_id " +
                    "JOIN job j ON j.id = stw.job_id " +
                    "JOIN job_category jc ON jc.id = j.job_category_id " +
                    "JOIN student s ON s.uid = stw.student_uid " +
                    "JOIN student_msg sm ON sm.uid = s.uid " +
                    "JOIN classes c ON c.id = sm.class_id " +
                    "JOIN major m ON m.id = c.major_id " +
                    "JOIN college c2 ON c2.id = m.college_id " +
                    "JOIN campus_term ct ON ct.id = j.campus_term_id " +
                    "WHERE des.is_deleted = 0 AND des.dept_employee_id = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deptEmployeeId);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                list.add(new DeptEvaluateRecordPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getInt(8),
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11),
                        resultSet.getString(12),
                        resultSet.getString(13)
                ));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }
}
